﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace TPA.DataAccess
{
    public class ProgramWriter
    {

        internal static string GetConnectionString(string fileName, bool noHeader)
        {
            var connString = "";
            var fileNameLower = fileName.ToLower();

            if (fileNameLower.EndsWith("xlsx") ||
                fileNameLower.EndsWith("xlsm"))
                connString = string.Format(
                    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml""",
                    fileName);
            else
                connString = string.Format(
                    @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0""",
                    fileName);

            //if (noHeader)
            //    connString = connString.Replace("HDR=YES", "HDR=NO");

            return connString;
        }

        public int WriteToExcel(string filename, List<KeyPoint> timeline)
        {
            // Establish a connection to the data source.
            string strconn = GetConnectionString(System.IO.Directory.GetCurrentDirectory()+"\\template.xlsx", true);
            System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(strconn);
            objConn.Open();

            // Add two records to the table named 'MyTable'.
            System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = "delete from [Sheet1$]";
            objCmd.ExecuteNonQuery();
            objCmd.CommandText = "Insert into [Sheet1$]" +
                " values ('Bill', 'Brown')";
            objCmd.ExecuteNonQuery();
            objCmd.CommandText = "Insert into [Sheet1$]" +
                " values ('Joe', 'Thomas')";
            objCmd.ExecuteNonQuery();

            // Close the connection.
            objConn.Close();
            return 1;
        }

    }


}
